package com.ssy.lingxi.report.dao.product;

import com.ssy.lingxi.report.model.bo.StatisticsNewlyAddedBO;
import com.ssy.lingxi.report.model.bo.tally.statistics.CommodityStatisticsTallyBO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 品牌DAO
 * @author 伍将
 * @date 2020/11/12
 * @version 2.0.0
 */
@Service
public class BrandDAO {

    @Autowired
    @Qualifier("productJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 统计新增商品
     * @author 伍将
     * @date 2020/11/12
     * @param startTime:
     * @param endTime:
     * @return java.util.List<com.ssy.lingxi.report.model.bo.StatisticsNewlyAddedBO>
     **/
    public List<StatisticsNewlyAddedBO> getNewlyAddedBrandByDay(Long startTime, Long endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" to_char(to_timestamp(apply_time/1000),'YYYY-MM-DD') date_time,count(id)")
                .append(" from ")
                .append(" product_brand ")
                .append(" where apply_time between " + startTime + " and " + endTime)
                .append(" and status>1")    //查询已提交状态
                .append(" group by date_time;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsNewlyAddedBO.class));
    }

    /**
     * 查询品牌计数统计数量
     * @author 伍将
     * @date 2020/11/26
     * @return:
     **/
    public List<CommodityStatisticsTallyBO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select member_id,member_role_id role_Id,")
                .append(" count(id) brandCount,")
                .append(" sum(case when status = 1 then 1 else 0 end) toBeBrandValifyCommitCount,")
                .append(" 0 toBeBrandValifyCount")
                .append(" FROM product_brand where type=1")
                .append(" group by member_id,member_role_id")
                .append(" union ")
                .append(" select 0 member_id,0 role_Id,")
                .append(" sum(case when status > 1 then 1 else 0 end) brandCount,")
                .append(" 0 toBeBrandValifyCommitCount,")
                .append(" sum(case when status = 2 then 1 else 0 end) toBeBrandValifyCount")
                .append(" FROM product_brand where type=2;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CommodityStatisticsTallyBO.class));
    }
}
